package com.ncloud.billing.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.ncloud.billing.entity.ChnlDetailBill;
import com.ncloud.billing.entity.ChnlRealBill;

@Mapper
public interface ChnlRealBillMapper{

	@Select("SELECT bill_id, chnl_id, user_id, order_id, cust_id, discnt_inst_id, cycle_id,fee, balance, bill_tag,date_format(create_time, '%Y-%m-%d %H:%i:%s' ) as create_time, create_user_id, "
			+ "date_format(update_time, '%Y-%m-%d %H:%i:%s' ) as update_time, update_user_id, remark FROM sys_chnl_realbill "
			+ "where chnl_id = #{chnl_id} ")
	@Results({
		@Result(column="bill_id",property="bill_id"),@Result(column="chnl_id",property="chnl_id"),@Result(column="user_id",property="user_id"),
		@Result(column="order_id",property="order_id"),@Result(column="cust_id",property="cust_id"),
		@Result(column="discnt_inst_id",property="discnt_inst_id"),@Result(column="cycle_id",property="cycle_id"),@Result(column="fee",property="fee"),
		@Result(column="balance",property="balance"),@Result(column="bill_tag",property="bill_tag"),
		@Result(column="create_time",property="create_time"),@Result(column="create_user_id",property="create_user_id"),
		@Result(column="update_time",property="update_time"),@Result(column="update_user_id",property="update_user_id"),
		@Result(column="remark",property="remark")})
	List<ChnlRealBill> getChnlRealBillByChnlID(Long chnl_id);
	/*@Select("SELECT bill_id, chnl_id, user_id, order_id, cust_id, discnt_inst_id, cycle_id,fee, balance, bill_tag,date_format(create_time, '%Y-%m-%d %H:%i:%s' ) as create_time, create_user_id, "
			+ "date_format(update_time, '%Y-%m-%d %H:%i:%s' ) as update_time, update_user_id, remark FROM sys_chnl_realbill "
			+ "where cycle_id = #{cycle_id} ")
	@Results({
		@Result(column="bill_id",property="bill_id"),@Result(column="chnl_id",property="chnl_id"),@Result(column="user_id",property="user_id"),
		@Result(column="order_id",property="order_id"),@Result(column="cust_id",property="cust_id"),
		@Result(column="discnt_inst_id",property="discnt_inst_id"),@Result(column="cycle_id",property="cycle_id"),@Result(column="fee",property="fee"),
		@Result(column="balance",property="balance"),@Result(column="bill_tag",property="bill_tag"),
		@Result(column="create_time",property="create_time"),@Result(column="create_user_id",property="create_user_id"),
		@Result(column="update_time",property="update_time"),@Result(column="update_user_id",property="update_user_id"),
		@Result(column="remark",property="remark")})
	List<ChnlRealBill> getChnlRealBillByCycleID(Integer cycle_id);*/
	
	@Select("SELECT t1.bill_id,0 as total_id, t1.chnl_id, "
			+ "(select t2.chnl_name from sys_chnl t2 where t2.chnl_id=t1.chnl_id) as chnl_name,"
			+ "t1.user_id,"
			+ "(select t3.sale_id from sys_sales t3 where t3.user_id=t1.user_id) as sale_id,"
			//+ "(select t4.sale_name from sys_sales t4 where t4.user_id=t1.user_id) as sale_name,"
			+ "(select t4.name from sys_user t4 where t4.id=t1.user_id) as sale_name,"
			+ "t1.order_id, t1.cust_id,"
			+ "(select t5.cust_name from bs_o_cust t5 where t5.cust_id=t1.cust_id) as cust_name,"
			+ "t1.discnt_inst_id,"
			+ "(select t6.discnt_id from bs_o_discnt t6 where t6.order_id=t1.order_id and t6.discnt_inst_id=t1.discnt_inst_id) as discnt_id,"
			+ "(select t7.discnt_name from bs_o_discnt t7 where t7.order_id=t1.order_id and t7.discnt_inst_id=t1.discnt_inst_id) as discnt_name,"
			+ "(select t8.prod_inst_id from bs_o_product t8 where t8.order_id=t1.order_id) as prod_inst_id,"
			+ "(select t9.prod_id from bs_o_product t9 where t9.order_id=t1.order_id) as prod_id,"
			+ "(select t10.prod_name from bs_o_product t10 where t10.order_id=t1.order_id) as prod_name,"
			+ "t1.cycle_id, t1.fee, t1.balance, t1.bill_tag,"
			+ "date_format(create_time, '%Y-%m-%d %H:%i:%s' ) as create_time, create_user_id,"
			+ "date_format(update_time, '%Y-%m-%d %H:%i:%s' ) as update_time, update_user_id, remark FROM sys_chnl_realbill "
			+ "where cycle_id = #{cycle_id} ")
	@Results({
		@Result(column="bill_id",property="bill_id"),@Result(column="total_id",property="total_id"),
		@Result(column="chnl_id",property="chnl_id"),@Result(column="chnl_name",property="chnl_name"),
		@Result(column="user_id",property="user_id"),@Result(column="sale_id",property="sale_id"),
		@Result(column="sale_name",property="sale_name"),@Result(column="order_id",property="order_id"),
		@Result(column="cust_id",property="cust_id"),@Result(column="cust_name",property="cust_name"),
		@Result(column="discnt_inst_id",property="discnt_inst_id"),@Result(column="discnt_id",property="discnt_id"),
		@Result(column="discnt_name",property="discnt_name"),@Result(column="prod_inst_id",property="prod_inst_id"),
		@Result(column="prod_id",property="prod_id"),@Result(column="prod_name",property="prod_name"),
		@Result(column="cycle_id",property="cycle_id"),@Result(column="fee",property="fee"),
		@Result(column="balance",property="balance"),@Result(column="bill_tag",property="bill_tag"),
		@Result(column="create_time",property="create_time"),@Result(column="create_user_id",property="create_user_id"),
		@Result(column="update_time",property="update_time"),@Result(column="update_user_id",property="update_user_id"),
		@Result(column="remark",property="remark")})
	List<ChnlDetailBill> getChnlRealBillByCycleID(Integer cycle_id);
	
	@Select("SELECT t1.bill_id,0 as total_id, t1.chnl_id, "
			+ "(select t2.chnl_name from sys_chnl t2 where t2.chnl_id=t1.chnl_id) as chnl_name,"
			+ "0 as parent_id,"
			+ "t1.user_id,"
			+ "(select t3.sale_id from sys_sales t3 where t3.user_id=t1.user_id) as sale_id,"
			//+ "(select t4.sale_name from sys_sales t4 where t4.user_id=t1.user_id) as sale_name,"
			+ "(select t4.name from sys_user t4 where t4.id=t1.user_id) as sale_name,"
			+ "t1.order_id, t1.cust_id,"
			+ "(select t5.cust_name from bs_o_cust t5 where t5.cust_id=t1.cust_id) as cust_name,"
			+ "t1.discnt_inst_id,"
			+ "(select t6.discnt_id from bs_o_discnt t6 where t6.order_id=t1.order_id and t6.discnt_inst_id=t1.discnt_inst_id) as discnt_id,"
			+ "(select t7.discnt_name from bs_o_discnt t7 where t7.order_id=t1.order_id and t7.discnt_inst_id=t1.discnt_inst_id) as discnt_name,"
			+ "(select t8.prod_inst_id from bs_o_product t8 where t8.order_id=t1.order_id) as prod_inst_id,"
			+ "(select t9.prod_id from bs_o_product t9 where t9.order_id=t1.order_id) as prod_id,"
			+ "(select t10.prod_name from bs_o_product t10 where t10.order_id=t1.order_id) as prod_name,"
			+ "t1.cycle_id, t1.fee, t1.balance, t1.bill_tag,"
			+ "date_format(create_time, '%Y-%m-%d %H:%i:%s' ) as create_time, create_user_id,"
			+ "date_format(update_time, '%Y-%m-%d %H:%i:%s' ) as update_time, update_user_id, remark FROM sys_chnl_realbill t1 "
			+ "where t1.cycle_id = #{0} and t1.bill_tag = '0' "
			/*+ "and t1.chnl_id in (SELECT chnl_id FROM  sys_chnl where chnl_id = #{1} "
			+ "union all SELECT chnl_id FROM  sys_chnl where parent_id = #{1})"*/
			+ "and FIND_IN_SET(t1.chnl_id,f_getChildChnl(#{1})) ")
	@Results({
		@Result(column="bill_id",property="bill_id"),@Result(column="total_id",property="total_id"),@Result(column="parent_id",property="parent_id"),
		@Result(column="chnl_id",property="chnl_id"),@Result(column="chnl_name",property="chnl_name"),
		@Result(column="user_id",property="user_id"),@Result(column="sale_id",property="sale_id"),
		@Result(column="sale_name",property="sale_name"),@Result(column="order_id",property="order_id"),
		@Result(column="cust_id",property="cust_id"),@Result(column="cust_name",property="cust_name"),
		@Result(column="discnt_inst_id",property="discnt_inst_id"),@Result(column="discnt_id",property="discnt_id"),
		@Result(column="discnt_name",property="discnt_name"),@Result(column="prod_inst_id",property="prod_inst_id"),
		@Result(column="prod_id",property="prod_id"),@Result(column="prod_name",property="prod_name"),
		@Result(column="cycle_id",property="cycle_id"),@Result(column="fee",property="fee"),
		@Result(column="balance",property="balance"),@Result(column="bill_tag",property="bill_tag"),
		@Result(column="create_time",property="create_time"),@Result(column="create_user_id",property="create_user_id"),
		@Result(column="update_time",property="update_time"),@Result(column="update_user_id",property="update_user_id"),
		@Result(column="remark",property="remark")})
	List<ChnlDetailBill> createChnlDetailBillByCycleID(Integer cycle_id,Long chnl_id);
	
	@Insert(value = "INSERT INTO sys_chnl_realbill (bill_id, chnl_id, user_id, order_id, cust_id, discnt_inst_id,cycle_id,fee, balance, bill_tag, create_time, create_user_id, update_time, update_user_id, remark)  "
									+ "values(#{bill_id},#{chnl_id},#{user_id},#{order_id},#{cust_id},#{discnt_inst_id},#{cycle_id},#{fee},#{balance},#{bill_tag},#{create_time},#{create_user_id},#{update_time},#{update_user_id},#{remark})")
    public int insertChnlRealBill(ChnlRealBill chnlRealBill);
	
	@Update(value = "UPDATE sys_chnl_realbill SET balance = #{balance}, bill_tag = #{bill_tag}, update_time = #{update_time}, update_user_id = #{update_user_id} WHERE bill_id = #{bill_id} ")
    public int updateChnlRealBill(ChnlRealBill chnlRealBill);
	
	@Update(value = "UPDATE sys_chnl_realbill SET bill_tag = '1', update_time = now(), update_user_id = #{0} WHERE cycle_id = #{1} and chnl_id = #{2} and bill_tag = '0'")
    public int updateChnlRealBillTag(Long update_user_id,Integer cycle_id,Long chnl_id);
	
	@Delete(value = " delete from sys_chnl_realbill where cycle_id = #{cycle_id} ")
    public int deleteChnlRealBillByCycleID(Integer cycle_id);
	@Delete(value = " delete from sys_chnl_realbill where bill_id = #{bill_id} ")
    public int deleteChnlRealBillByBillId(Long bill_id);
}
